﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Pantry.Commons;

namespace Pantry.DataAccess
{
    public class PantryDao:IDisposable
    {
        #region Properties
        private const string SqlCheckUnique = @"
            SELECT COUNT({0})
            FROM {1}
            WHERE {2}= '{3}'";
        private const string SqlCheckUniqueForAmend = @"
            SELECT COUNT({0})
            FROM {1}
            WHERE {2}='{3}'
                AND {4} != {5}";
        private const string SqlChangeIsActive = @"
            UPDATE {0}
            SET {1} = '{2}'
            WHERE {3}= {4}";
        protected SqlConnection SqlConnect { set; get; }
        private readonly bool throwError;
        #endregion
         
        #region Constructor
        public PantryDao(SqlConnection sqlConnection)
        {
            SqlConnect = sqlConnection;
            throwError = true;
        }

        public PantryDao()
        {
            SqlConnect = SqlConnectionTool.SqlGetConnection();
            throwError = true;
        }
        #endregion

        #region Public Method
        public int ChangeIsActive(string table,string fieldName,string fieldValue,string fieldId,int valueId)
        {
            var sqlCommand = String.Format(CultureInfo.CurrentCulture,SqlChangeIsActive , table, fieldName, fieldValue,fieldId,valueId);
            if (throwError)
            {
                return SqlHelper.ExecuteNonQuery(SqlConnect, CommandType.Text, sqlCommand);
            }

            try
            {
                return SqlHelper.ExecuteNonQuery(SqlConnect, CommandType.Text, sqlCommand);
            }
            catch (SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }
            catch (InvalidOperationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }

            return -1;
        } 

        public int CheckUnique(string table, string fieldName, string newValue)
        {
            if (string.IsNullOrEmpty(table) || string.IsNullOrEmpty(fieldName) || string.IsNullOrEmpty(newValue))
            {
                return 0;
            }

            var sqlCommand = String.Format(CultureInfo.InvariantCulture, SqlCheckUnique, fieldName, table, fieldName, newValue);
            if (throwError)
            {
                return (int)SqlHelper.ExecuteScalar(SqlConnect, CommandType.Text, sqlCommand);
            }

            try
            {
                return (int)SqlHelper.ExecuteScalar(SqlConnect, CommandType.Text, sqlCommand);
            }
            catch (SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }
            catch (InvalidOperationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }

            return -1;
        }
        #endregion

        #region Virtual Method
        public virtual DataTable RetrieveAllData()
        {
            return null;
        }

        public virtual DataTable RetrieveData(string conditionField,string conditionValue,SqlDbType sqlType)
        {
            return null;
        }

        public virtual object RetrieveEntity(int id)
        {
            return null;
        }

        public virtual int Insert(object sender)
        {
            return 0;
        }

        public virtual int Update(object sender)
        {
            return 0;
        }

        public virtual int Delete(int id)
        {
            return 0;
        }
        #endregion

        #region Method nees protected
        protected DataTable RetrieveData(string sqlCommand, params SqlParameter[] parameters)
        {
            if (throwError)
            {
                var dataSet = SqlHelper.ExecuteDataSet(SqlConnect, CommandType.Text, sqlCommand, parameters);
                return dataSet.Tables.Count < 1 ? null : dataSet.Tables[0];
            }

            try
            {
                var dataSet = SqlHelper.ExecuteDataSet(SqlConnect, CommandType.Text, sqlCommand, parameters);
                return dataSet.Tables.Count<1 ? null : dataSet.Tables[0];
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }
            catch (InvalidOperationException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }

            return null;
        }

        protected int ExecuteNonQuery(string sqlCommand, params SqlParameter[] parameters)
        {
            if (throwError)
            {
                return SqlHelper.ExecuteNonQuery(SqlConnect, CommandType.Text, sqlCommand, parameters);
            }

            try
            {
                return SqlHelper.ExecuteNonQuery(SqlConnect, CommandType.Text, sqlCommand, parameters);
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }
            catch (InvalidOperationException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }

            return -1;
        }

        protected int ExecuteScalar(string sqlCommand, params SqlParameter[] parameters)
        {
            if (throwError)
            {
                return (int)SqlHelper.ExecuteScalar(SqlConnect, CommandType.Text, sqlCommand, parameters);
            }

            try
            {
                return (int)SqlHelper.ExecuteScalar(SqlConnect, CommandType.Text,sqlCommand,parameters);
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }
            catch (InvalidOperationException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }

            return -1;
        }

        public int CheckUniqueForAmend(string table, string fieldName, string newValue, string fieldId, int valueId)
        {
            if (string.IsNullOrEmpty(table) || string.IsNullOrEmpty(fieldName) || string.IsNullOrEmpty(newValue) || string.IsNullOrEmpty(fieldId))
            {
                return 0;
            }

            var sqlCommand = String.Format(CultureInfo.InvariantCulture, SqlCheckUniqueForAmend, fieldName, table, fieldName, newValue, fieldId, valueId);
            if (throwError)
            {
                return (int)SqlHelper.ExecuteScalar(SqlConnect, CommandType.Text, sqlCommand);
            }

            try
            {
                return (int)SqlHelper.ExecuteScalar(SqlConnect, CommandType.Text, sqlCommand);
            }
            catch (SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }
            catch (InvalidOperationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }

            return -1;
        }
      
        //not tested
        protected Dictionary<string, object> RetrieveFirstRow(string sql)
        {
            var firstRow = new Dictionary<string, object>();
            SqlDataReader sqlDataReader = null;
            var command = new SqlCommand(sql, SqlConnect);
            try
            {
                sqlDataReader = command.ExecuteReader();
            }
            catch (SqlException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }
            catch (InvalidOperationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
            }

            if (sqlDataReader == null || (!sqlDataReader.HasRows || !sqlDataReader.Read()))
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                    sqlDataReader.Dispose();
                }

                return null;
            }

            for (var column = 0; column < sqlDataReader.FieldCount; column++)
            {
                try
                {
                    firstRow.Add(sqlDataReader.GetName(column), sqlDataReader[column]);
                }
                catch (ArgumentNullException e)
                {
                    ExceptionHandler.WriteErrorLog(e);
                }
                catch (ArgumentException e)
                {
                    ExceptionHandler.WriteErrorLog(e);
                }
            }

            sqlDataReader.Close();
            sqlDataReader.Dispose();
            return firstRow;
        }
        #endregion

        #region Dispose Method
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposing) return;
            if (SqlConnect == null) return;
            SqlConnect.Dispose();
            SqlConnect = null;
        }
        #endregion
    }
}
